package db;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author Gjun
 */
public class SQLC {
    static{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception ex) {
            System.out.println(ex);
        }
    }
    Connection getConnection() throws Exception{ 
       Context x = new InitialContext();
          Context y = (Context)x.lookup("java:/comp/env");
          DataSource z = (DataSource)y.lookup("jdbc/mssql");
          Connection con = z.getConnection();
          return con;
    }
    
    public int modify(String sql, String[] param) throws Exception{
        int x=0;
        Connection sqc = null;
        try{
            sqc = getConnection();
            java.sql.PreparedStatement ps = null;
            try{
                ps = sqc.prepareStatement(sql);
                for(int i=0;i<param.length;i++)
                    ps.setString(i+1, param[i]);
                x = ps.executeUpdate();
            }finally{
                ps.close();
            }
        }finally{
            sqc.close();
        }
        return x;
    }
    
     public ArrayList<HashMap> query(String sql) throws Exception{
        ArrayList<HashMap> list = new ArrayList<HashMap>();
        Connection sqc = null;
        try{
            sqc = getConnection();
            java.sql.PreparedStatement ps = null;
            try{
                ps = sqc.prepareStatement(sql);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    java.sql.ResultSetMetaData meta = rs.getMetaData();
                    int count = meta.getColumnCount();
                    while(rs.next()){
                        HashMap map = new HashMap();
                        for(int i=1;i<= count;i++){
                            String key = meta.getColumnName(i);
                            String value = rs.getString(i);
                            map.put(key,value);
                        }
                        list.add(map);
                    }
                }finally{
                    rs.close();
                }
            }finally{
                ps.close();
            }
        }finally{
            sqc.close();
        }
        return list;
    }
    
    public ArrayList<HashMap> Iquery(String sql,int start,int amount)throws Exception{
        ArrayList<HashMap> list = new ArrayList<HashMap>();
        Connection sqc = null;
        try{
            sqc = getConnection();
            java.sql.PreparedStatement ps = null;
            try{
                String command = sql+" order by id offset "+start+" rows fetch next "+amount+" rows only";
                ps = sqc.prepareStatement(command);
                
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    java.sql.ResultSetMetaData meta = rs.getMetaData();
                    int count = meta.getColumnCount();
                    while(rs.next()){
                        HashMap map = new HashMap();
                        for(int i=1;i<= count;i++){
                            String key = meta.getColumnName(i);
                            String value = rs.getString(i);
                            map.put(key,value);
                        }
                        list.add(map);
                    }
                }finally{
                    rs.close();
                }
            }finally{
                ps.close();
            }
        }finally{
            sqc.close();
        }
        return list;
    }
    
    public int insert(String name,String price,String num,String say) throws Exception{
        int x=0;
        Connection cn=null;
        try{
            cn=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                    String sql = "INSERT INTO 產品維護(產品名稱,產品價格,庫存量,產品介紹) VALUES('"+name+"','"+price+"','"+num+"','"+say+"')";
                    ps=cn.prepareStatement(sql);                    
                    x=ps.executeUpdate();
            }finally{
                ps.close();
            }
        }finally{
            cn.close();
        }        
        return x;
    }
    
    public String  getLast()throws Exception
   {
       String num=null;
     Connection con=null;
            comm.comm c=null;
        try{
            con=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                 String sql="select top 1 id from 產品維護 order by id desc";               
                ps=con.prepareStatement(sql);   
            java.sql.ResultSet rs=null;
            try{
                rs=ps.executeQuery();
              
              while(rs.next()){
              num=rs.getString(1);
              }
            }finally{
                rs.close();
            }
                    
        }finally{
            ps.close();
        }
        }finally{
        con.close();
        }                 
       return num;
   
   } 
}
